package simulaco.dao;

import gminet.infra.dao.DataAccessObject;
import gminet.infra.db.TransactionException;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import simulaco.dto.Questao;
import simulaco.dto.QuestaoItem;
import simulaco.dto.Situacao;

public class QuestaoItemDAO extends DataAccessObject {

    public static QuestaoItem findByPK(QuestaoItem questaoItem, Connection conn) throws TransactionException {

        return findByPK(questaoItem.getQuestao().getConcurso().getCodigo(), questaoItem.getQuestao().getMateria()
                .getCodigo(), questaoItem.getQuestao().getCodigo(), questaoItem.getCodigo(), conn);
    }

    public static QuestaoItem findByPK(int concursoId, int materiaId, int questaoId, int itemId, Connection conn)
            throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_CONCURSO       CD_CONCURSO       ");
        sbSql.append("     , A.CD_MATERIA        CD_MATERIA        ");
        sbSql.append("     , A.CD_QUESTAO        CD_QUESTAO        ");
        sbSql.append("     , A.CD_ITEM           CD_ITEM           ");
        sbSql.append("     , A.TX_INDENTIFICACAO TX_INDENTIFICACAO ");
        sbSql.append("     , A.TX_ITEM           TX_ITEM           ");
        sbSql.append("     , A.TX_COMENTARIO     TX_COMENTARIO     ");
        sbSql.append("     , A.ID_ITEM_CORRETO   ID_ITEM_CORRETO   ");
        sbSql.append("     , A.ID_SITUACAO       ID_SITUACAO       ");
        sbSql.append("  FROM QUESTAO_ITEM    A ");
        sbSql.append(" WHERE A.CD_CONCURSO = ? ");
        sbSql.append("   AND A.CD_MATERIA  = ? ");
        sbSql.append("   AND A.CD_QUESTAO  = ? ");
        sbSql.append("   AND A.CD_ITEM     = ? ");

        QuestaoItem item = null;
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, concursoId);
            setInt(ps, 2, materiaId);
            setInt(ps, 3, questaoId);
            setInt(ps, 4, itemId);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                item = fromResultSet(rs);
            }
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoItemDAO.findByPK (" + sqle.getMessage() + ")");
        }
        return item;
    }

    public static List<QuestaoItem> findByQuestao(Questao questao, Connection conn) throws TransactionException {

        return findByQuestao(questao.getConcurso().getCodigo(), questao.getMateria().getCodigo(), questao.getCodigo(),
                conn);
    }

    public static List<QuestaoItem> findByQuestao(int concursoId, int materiaId, int questaoId, Connection conn)
            throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("SELECT A.CD_CONCURSO       CD_CONCURSO       ");
        sbSql.append("     , A.CD_MATERIA        CD_MATERIA        ");
        sbSql.append("     , A.CD_QUESTAO        CD_QUESTAO        ");
        sbSql.append("     , A.CD_ITEM           CD_ITEM           ");
        sbSql.append("     , A.TX_INDENTIFICACAO TX_INDENTIFICACAO ");
        sbSql.append("     , A.TX_ITEM           TX_ITEM           ");
        sbSql.append("     , A.TX_COMENTARIO     TX_COMENTARIO     ");
        sbSql.append("     , A.ID_ITEM_CORRETO   ID_ITEM_CORRETO   ");
        sbSql.append("     , A.ID_SITUACAO       ID_SITUACAO       ");
        sbSql.append("  FROM QUESTAO_ITEM    A ");
        sbSql.append(" WHERE A.CD_CONCURSO = ? ");
        sbSql.append("   AND A.CD_MATERIA  = ? ");
        sbSql.append("   AND A.CD_QUESTAO  = ? ");

        List<QuestaoItem> itemsList = new ArrayList<QuestaoItem>(100);
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            setInt(ps, 1, concursoId);
            setInt(ps, 2, materiaId);
            setInt(ps, 3, questaoId);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                QuestaoItem item = fromResultSet(rs);
                itemsList.add(item);
            }
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoItemDAO.findAll (" + sqle.getMessage() + ")");
        }
        return itemsList;
    }

    public static void insert(QuestaoItem item, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("INSERT INTO QUESTAO_ITEM ");
        sbSql.append("     ( CD_CONCURSO       ");
        sbSql.append("     , CD_MATERIA        ");
        sbSql.append("     , CD_QUESTAO        ");
        sbSql.append("     , CD_ITEM           ");
        sbSql.append("     , TX_INDENTIFICACAO ");
        sbSql.append("     , TX_ITEM           ");
        sbSql.append("     , TX_COMENTARIO     ");
        sbSql.append("     , ID_ITEM_CORRETO   ");
        sbSql.append("     , ID_SITUACAO       ");
        sbSql.append(") VALUES (?,?,?,?,?,?,?,?,?)");

        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, item.getQuestao().getConcurso().getCodigo());
            setInt(ps, pos++, item.getQuestao().getMateria().getCodigo());
            setInt(ps, pos++, item.getQuestao().getCodigo());
            setInt(ps, pos++, item.getCodigo());
            setString(ps, pos++, item.getIdentificador());
            setString(ps, pos++, item.getTexto());
            setString(ps, pos++, item.getComentario(), true);
            setByte(ps, pos++, (byte) (item.isItemCorreto() ? 1 : 0));
            setInt(ps, pos++, item.getSituacao().getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoItemDAO.insert (" + sqle.getMessage() + ")");
        }
    }

    public static void update(QuestaoItem item, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("UPDATE QUESTAO_ITEM          ");
        sbSql.append("   SET TX_INDENTIFICACAO = ? ");
        sbSql.append("     , TX_ITEM           = ? ");
        sbSql.append("     , TX_COMENTARIO     = ? ");
        sbSql.append("     , ID_ITEM_CORRETO   = ? ");
        sbSql.append("     , ID_SITUACAO       = ? ");
        sbSql.append(" WHERE CD_CONCURSO       = ? ");
        sbSql.append("   AND CD_MATERIA        = ? ");
        sbSql.append("   AND CD_QUESTAO        = ? ");
        sbSql.append("   AND CD_ITEM           = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setString(ps, pos++, item.getIdentificador());
            setString(ps, pos++, item.getTexto());
            setString(ps, pos++, item.getComentario(), true);
            setByte(ps, pos++, (byte) (item.isItemCorreto() ? 1 : 0));
            setInt(ps, pos++, item.getSituacao().getCodigo());
            setInt(ps, pos++, item.getQuestao().getConcurso().getCodigo());
            setInt(ps, pos++, item.getQuestao().getMateria().getCodigo());
            setInt(ps, pos++, item.getQuestao().getCodigo());
            setInt(ps, pos++, item.getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoItemDAO.update (" + sqle.getMessage() + ")");
        }
    }

    public static void delete(QuestaoItem item, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("DELETE FROM QUESTAO_ITEM ");
        sbSql.append(" WHERE CD_CONCURSO = ? ");
        sbSql.append("   AND CD_MATERIA  = ? ");
        sbSql.append("   AND CD_QUESTAO  = ? ");
        sbSql.append("   AND CD_ITEM     = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, item.getQuestao().getConcurso().getCodigo());
            setInt(ps, pos++, item.getQuestao().getMateria().getCodigo());
            setInt(ps, pos++, item.getQuestao().getCodigo());
            setInt(ps, pos++, item.getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoItemDAO.delete (" + sqle.getMessage() + ")");
        }
    }

    public static void deleteByQuestao(Questao questao, Connection conn) throws TransactionException {

        StringBuffer sbSql = new StringBuffer();
        sbSql.append("DELETE FROM QUESTAO_ITEM ");
        sbSql.append(" WHERE CD_CONCURSO = ? ");
        sbSql.append("   AND CD_MATERIA  = ? ");
        sbSql.append("   AND CD_QUESTAO  = ? ");
        try {
            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, questao.getConcurso().getCodigo());
            setInt(ps, pos++, questao.getMateria().getCodigo());
            setInt(ps, pos++, questao.getCodigo());
            ps.executeUpdate();
            close(ps);
        } catch (SQLException sqle) {
            throw new TransactionException("QuestaoItemDAO.deleteByQuestao (" + sqle.getMessage() + ")");
        }
    }

    public static int getNextCodigo(Questao questao, Connection conn) throws TransactionException {

        try {
            StringBuffer sbSql = new StringBuffer();
            sbSql.append("SELECT MAX(CD_ITEM) AS CD_MAX ");
            sbSql.append("  FROM QUESTAO_ITEM    ");
            sbSql.append(" WHERE CD_CONCURSO = ? ");
            sbSql.append("   AND CD_MATERIA  = ? ");
            sbSql.append("   AND CD_QUESTAO  = ? ");

            PreparedStatement ps = conn.prepareStatement(sbSql.toString());
            int pos = 1;
            setInt(ps, pos++, questao.getConcurso().getCodigo());
            setInt(ps, pos++, questao.getMateria().getCodigo());
            setInt(ps, pos++, questao.getCodigo());
            ResultSet rs = ps.executeQuery();
            int max = 0;
            if (rs.next()) {
                max = rs.getInt("CD_MAX");
            }
            close(rs);
            return max + 1;
        } catch (SQLException sqlEx) {
            throw new TransactionException(sqlEx);
        }
    }

    private static QuestaoItem fromResultSet(ResultSet rs) throws SQLException {

        // QUESTAO ITEM
        QuestaoItem item = new QuestaoItem();
        item.getQuestao().getConcurso().setCodigo(getInt(rs, "CD_CONCURSO"));
        item.getQuestao().getMateria().setCodigo(getInt(rs, "CD_MATERIA"));
        item.getQuestao().setCodigo(getInt(rs, "CD_QUESTAO"));
        item.setCodigo(getInt(rs, "CD_ITEM"));
        item.setIdentificador(getString(rs, "TX_INDENTIFICACAO"));
        item.setTexto(getString(rs, "TX_ITEM"));
        item.setComentario(getString(rs, "TX_COMENTARIO"));
        item.setItemCorreto(getByte(rs, "ID_ITEM_CORRETO") == 1);
        item.setSituacao(Situacao.getSituacaoById(getByte(rs, "ID_SITUACAO")));
        //
        return item;
    }

}
